package com.han.hotelplat.service.datacenter.attendance.impl;

import java.text.DecimalFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.HashSet;
import java.util.List;
import java.util.Map;
import java.util.Set;
import java.util.TreeMap;

import org.apache.commons.lang.StringUtils;
import org.hibernate.Hibernate;
import org.hibernate.type.NullableType;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import com.han.hotelplat.comm.base.EntityManage;
import com.han.hotelplat.comm.utils.AreaHelp;
import com.han.hotelplat.comm.utils.DateUtil;
import com.han.hotelplat.model.AttendanceModel;
import com.han.hotelplat.pojo.hotel.Hotel;
import com.han.hotelplat.service.datacenter.attendance.AttendanceService;
import com.han.hotelplat.service.hotel.HotelService;

@Service
public class AttendanceServiceImpl implements AttendanceService{
	@Autowired
	private  EntityManage entityManage;
	@Autowired 
	private HotelService hotelService;
	
	private DecimalFormat   fnum  =   new  DecimalFormat("##0.0");  
	private DecimalFormat   moneyfnum  =   new  DecimalFormat("##0.00");
	@Override
	public Map<String, Object> getAttendanceTotal(String startTime,
			String endTime, Integer type) {
		Map<String, Object> data = new HashMap<String, Object>();
		int days = 1;
		StringBuffer sql = new StringBuffer("SELECT SUM(ct.roomcount),COUNT(ct.emid), ct.result FROM tb_child_task as ct, tb_task as t, tb_hotel as h WHERE ct.taskid=t.taskid and t.hotelid=h.id ");
		if(type != null && type.intValue() >= 0){
			switch (type) {
			case 0:
				sql.append(" AND TO_DAYS(date_format(ct.creattime,'%Y-%m-%d')) = TO_DAYS(now()) ");
				days = 1;
				break;
			case 1:
				sql.append(" AND YEARWEEK(date_format(ct.creattime,'%Y-%m-%d'),1) = YEARWEEK(now(),1) ");
				days = 7;
				break;
			case 2:
				sql.append(" AND DATE_FORMAT( ct.creattime, '%Y%m' ) = DATE_FORMAT( CURDATE() , '%Y%m' ) ");
				days = DateUtil.getTotalDaysForMonth(new Date());
				break;
			default:
				
				break;
			}
		}else{
			if(StringUtils.isNotBlank(startTime)){
				sql.append(" AND ct.creattime >= '"+startTime+"'");
			}else{
				data.put("result", -1);
				data.put("msg", "请输入起始日期");
				return data;
			}
			
			if(StringUtils.isNotBlank(endTime)){
				sql.append(" AND ct.creattime <= '"+endTime+" 23:59:59'");
				days = DateUtil.daysDistance(DateUtil.parseYMD(startTime), DateUtil.parseYMD(endTime))+1;
			}else{
				days = DateUtil.daysDistance(DateUtil.parseYMD(startTime), new Date())+1;
			}
		}
		sql.append(" AND ct.roomcount>0 GROUP BY ct.result");
		
		List<Object> ctli = entityManage.findBySql(sql.toString());
		float sumRoomcount = 0f;//总房量（已提交）
		int sumUsercount = 0;//总人数
		float authRoomcount = 0f;//已确认的房量
		for(int i= 0 ; i<ctli.size(); i++){
			Object[] ory = (Object[]) ctli.get(i);
			if(ory[0] != null){//房量
				sumRoomcount = sumRoomcount + Float.parseFloat(ory[0].toString());
			}
			if(ory[1] != null){//人数
				sumUsercount = sumUsercount + Integer.parseInt(ory[1].toString());
			}
			if(ory[2] != null){//考勤状态
				if(Integer.parseInt(ory[2].toString()) == 3 || Integer.parseInt(ory[2].toString()) == 4){
					authRoomcount = authRoomcount +  Float.parseFloat(ory[0].toString());
				}
			}
		}
		
		data.put("result", 0);	
		data.put("sumRoomcount", fnum.format(sumRoomcount));	//已提交房量
		data.put("authRoomcount", fnum.format(authRoomcount));//已确认房量
		data.put("sumUsercount", sumUsercount);//提交人数
		if(sumUsercount == 0){
			data.put("avgUserRoomcount", 0);//人均房量
		}else{
			data.put("avgUserRoomcount", fnum.format(sumRoomcount/sumUsercount));//人均房量
		}
		data.put("avgDayRoomcount", fnum.format(sumRoomcount/days));//日均房量
		return data;
	}

	@Override
	public Map<String, Object> getAttendanceGroupByArea(String startTime,
			String endTime, Integer type, Map<String, Object> data, Integer groupArea ) {
		String aCode = "substr(h.area,1,4 )";
		if(groupArea != null && groupArea.intValue() == 1){//按市级归类
			aCode = "substr(h.area,1,2)";
		}else if(groupArea == null || groupArea.intValue() == 2){//按市级归类
			aCode = "substr(h.area,1,4)";
		}else if(groupArea != null && groupArea.intValue() == 3){//按区级归类
			aCode = "substr(h.area,1,6)";
		}
		
		//查询已注册的所有酒店的区域编码
		String sql1 = "SELECT "+aCode+" FROM tb_hotel AS h group by "+aCode;
		List<Object> cityLi = entityManage.findBySql(sql1.toString());
		Set<String> cityCode = new HashSet<String>();
		for(int i= 0 ; i<cityLi.size(); i++){
			if(cityLi.get(i) == null){
				continue;
			}
			cityCode.add(cityLi.get(i).toString());
		}
		Set<String> zeroCityCode = new HashSet<String>();//房量为0的城市
		
		StringBuffer sql = new StringBuffer("SELECT  SUM(ct.roomcount) AS roomcount,"+aCode+" FROM tb_task AS t, tb_child_task AS ct, tb_hotel AS h WHERE ct.taskid=t.taskid AND h.id=t.hotelid ");
		if(type != null && type.intValue() >= 0){
			switch (type) {
			case 0:
				sql.append(" AND TO_DAYS(date_format(ct.creattime,'%Y-%m-%d')) = TO_DAYS(now()) ");
				break;
			case 1:
				sql.append(" AND YEARWEEK(date_format(ct.creattime,'%Y-%m-%d'),1) = YEARWEEK(now(),1) ");
				break;
			case 2:
				sql.append(" AND DATE_FORMAT( ct.creattime, '%Y%m' ) = DATE_FORMAT( CURDATE() , '%Y%m' ) ");
				break;
			default:
				if(StringUtils.isBlank(startTime) && StringUtils.isBlank(endTime)){
					sql.append(" AND TO_DAYS(date_format(ct.creattime,'%Y-%m-%d')) = TO_DAYS(now()) ");
				}
				break;
			}
		}else{
			if(StringUtils.isNotBlank(startTime)){
				sql.append(" AND ct.creattime >= '"+startTime+"'");
			}else{
				data.put("result", -1);
				data.put("msg", "请输入起始日期");
				return data;
			}
			
			if(StringUtils.isNotBlank(endTime)){
				sql.append(" AND ct.creattime <= '"+endTime+" 23:59:59'");
			}
		}
		sql.append(" AND ct.roomcount>0 group by "+aCode+" ORDER BY roomcount ASC");
		List<Object> ctli = entityManage.findBySql(sql.toString());
		Set<String> tmp = new HashSet<String>();
		JSONArray array = new JSONArray();
		for(int i= 0 ; i<ctli.size(); i++){
			String code = "0";
			float count = 0;
			Object[] ory = (Object[]) ctli.get(i);
			if(ory[0] != null){//房量
				count = Float.parseFloat(ory[0].toString());
			}
			if(ory[1] != null){//地区编码
				code = ory[1].toString();
			}
			JSONObject js = new JSONObject();
			Integer area = 0;
			if(groupArea != null && groupArea.intValue() == 1){//按省级归类
				area = Integer.parseInt(code)*10000;
				js.put("areaName", AreaHelp.getInstance().getProvince(area));
			}else if(groupArea == null || groupArea.intValue() == 2){//按市级归类
				area = Integer.parseInt(code)*100;
				js.put("areaName", AreaHelp.getInstance().getCity(area));
			}else if(groupArea != null && groupArea.intValue() == 3){//按区级归类
				area = Integer.parseInt(code);
				js.put("areaName", AreaHelp.getInstance().getAreaModel(code).getName());
			}
			
			js.put("area", area);
			js.put("count", count);
			array.add(js);
			tmp.add(code);
		}
		JSONArray array2 = new JSONArray();
		for(String areaCode : cityCode){
			if(!tmp.contains(areaCode)){//未提交过房量的地区
				JSONObject js = new JSONObject();
				Integer area = 0;
				if(groupArea != null && groupArea.intValue() == 1){//按省级归类
					area = Integer.parseInt(areaCode)*10000;
					js.put("areaName", AreaHelp.getInstance().getProvince(area));
				}else if(groupArea == null || groupArea.intValue() == 2){//按市级归类
					area = Integer.parseInt(areaCode)*100;
					js.put("areaName", AreaHelp.getInstance().getCity(area));
				}else if(groupArea != null && groupArea.intValue() == 3){//按区级归类
					area = Integer.parseInt(areaCode);
					js.put("areaName", AreaHelp.getInstance().getAreaModel(areaCode).getName());
				}
				js.put("area", area);
				js.put("count", 0);
				array2.add(js);
			}
		}
		array2.addAll(array);
		data.put("areaRoomcount", array2);
		return data;
	}

	@Override
	public Map<String, Object> getAttendanceGroupByDate(String startTime,
			String endTime, Integer type, Integer areaCode) {
		TreeMap<String, Object> tmpData  = new TreeMap<String, Object>();
		Map<String, Object> data  = new HashMap<String, Object>();
		List<JSONObject> list  = new ArrayList<JSONObject>();
		StringBuffer sql = new StringBuffer("SELECT ct.creattime, ct.result, SUM(ct.roomcount) AS roomcount, COUNT(ct.emid) AS usercount FROM tb_task AS t, tb_child_task AS ct, tb_hotel AS h WHERE ct.taskid=t.taskid AND h.id=t.hotelid ");
		StringBuffer sql2 = new StringBuffer("SELECT ct.creattime,COUNT(DISTINCT h.id) FROM tb_task AS t, tb_child_task AS ct, tb_hotel AS h WHERE ct.taskid=t.taskid AND h.id=t.hotelid ");
		Date startDate = new Date();
		Date endDate = new Date();
		int days = 0;
		if(type != null && type>=0){
			switch (type) {
			case 0://过去7天
				startDate = DateUtil.addDay(new Date(), -6);
				startTime = DateUtil.formatYMD(startDate);
				days = 7;
				break;
			case 1://过去30天
				startDate = DateUtil.addDay(new Date(), -29);
				startTime = DateUtil.formatYMD(startDate);
				days = 30;
				break;
			default:
				startDate = DateUtil.addDay(new Date(), -6);
				startTime = DateUtil.formatYMD(startDate);
				days = 7;
				break;
			}
			sql.append(" AND ct.creattime >= '"+startTime+"'");
			sql2.append(" AND ct.creattime >= '"+startTime+"'");
		}else{
			if(StringUtils.isNotBlank(startTime)){
				sql.append(" AND ct.creattime >= '"+startTime+"'");
				sql2.append(" AND ct.creattime >= '"+startTime+"'");
			}else{
				data.put("code", -1);
				data.put("msg", "请输入起始时间");
			}
			
			if(StringUtils.isNotBlank(endTime)){
				sql.append(" AND ct.creattime <= '"+endTime+" 23:59:59'");
				sql2.append(" AND ct.creattime <= '"+endTime+" 23:59:59'");
				days = DateUtil.daysDistance(DateUtil.parseYMD(startTime), DateUtil.parseYMD(endTime))+1;
				startDate = DateUtil.parseYMD(startTime);
				endDate = DateUtil.parseYMD(endTime);
			}else{
				days = DateUtil.daysDistance(DateUtil.parseYMD(startTime), new Date())+1;
				startDate = DateUtil.parseYMD(startTime);
			}
		}
		
		if(areaCode != null && areaCode.intValue()>100000){//区域码至少6位
			String w_areaCode = areaCode.toString();
			int x=0;
			String w_area="";
			if(w_areaCode.endsWith("0000")) {
				//精度 省份
				x=9999;
			}else if(w_areaCode.endsWith("00")) {
				//精度 市
				x=99;
			}else {
				//精度 区
				x=0;
			}
			//要的   - 条件  >=0
			//410610-410000 <= 9999
			//410610-410600 <= 99
			//410610-410610 == 0
			w_area=" and convert(h.area,signed) - "+areaCode +" >=0 ";
			w_area+=" and convert(h.area,signed) - "+areaCode +" <= "+x;
			sql.append(w_area);
			sql2.append(w_area);
		}
		sql.append(" AND ct.roomcount>0 group by ct.creattime, ct.result ORDER BY ct.creattime DESC ");
		sql2.append(" AND ct.roomcount>0 group by ct.creattime ORDER BY ct.creattime DESC ");
		
		List<Object> li = entityManage.findBySql(sql2.toString());
		Map<String, Integer> hotelCount = new HashMap<String,Integer>();
		for(int i= 0 ; i<li.size(); i++){
			String date = "";
			Object[] ory = (Object[]) li.get(i);
			if(ory[0] != null){//日期
				date = DateUtil.formatYMD((Date)ory[0]);
			}
			if(ory[1] != null){//数量
				hotelCount.put(date, Integer.parseInt(ory[1].toString()));
			}else{
				hotelCount.put(date, 0);
			}
		}
		List<Object> ctli = entityManage.findBySql(sql.toString());
		Map<String, Float> roomCount = new HashMap<String,Float>();
		Map<String, Integer> userCount = new HashMap<String,Integer>();
		Map<String, Float> autRroomCount = new HashMap<String,Float>();
		for(int i= 0 ; i<ctli.size(); i++){
			Float rc = 0f;//提交的总房量
			Float arc = 0f;//已确认的房量
			Integer user = 0;
			String date = "";
			Object[] ory = (Object[]) ctli.get(i);
			if(ory[0] != null){//日期
				date = DateUtil.formatYMD((Date)ory[0]);
				user = userCount.get(date);
				rc = roomCount.get(date);
				arc = autRroomCount.get(date);
			}
			
			if(ory[1] != null && ory[2] != null){//审核结果,房量
				rc = (rc == null ? 0 : rc) + Float.parseFloat(ory[2].toString());
				if(Integer.parseInt(ory[1].toString()) == 3
						|| Integer.parseInt(ory[1].toString()) == 4){//已确认的房量
					arc = (arc == null ? 0 : arc) + Float.parseFloat(ory[2].toString());
				}
				roomCount.put(date, rc);
				autRroomCount.put(date, arc);
			}
			
			if(ory[3] != null){//人数
				user = (user == null ? 0 : user) + Integer.parseInt(ory[3].toString());
				userCount.put(date, user);
			}
			JSONObject js = new JSONObject();
			js.put("sumRoomCount", fnum.format(rc));
			js.put("authRoomCount", fnum.format(arc == null ? 0 : arc));
			js.put("userCount", user);
			js.put("hotelCount", hotelCount.get(date));
			js.put("avgUserRoomcount", fnum.format(rc/user));//人均房量
			js.put("avgDayRoomcount", fnum.format(rc/days));//日均房量
			tmpData.put(date, js);
		}
		List<Date> allDate = DateUtil.getBetweenDates(startDate, endDate);
		for(Date date : allDate){
			JSONObject tmpjs = new JSONObject();
			
			JSONObject js = (JSONObject) tmpData.get(DateUtil.formatYMD(date));
			if(js != null){
				tmpjs.put("date", DateUtil.formatYMD(date));
				tmpjs.put("attendance",js);
			}else{
				js = new JSONObject();
				js.put("sumRoomCount", 0);
				js.put("authRoomCount", 0);
				js.put("userCount", 0);
				js.put("hotelCount", 0);
				js.put("avgUserRoomcount", 0);//人均房量
				js.put("avgDayRoomcount", 0);//日均房量
				tmpjs.put("date", DateUtil.formatYMD(date));
				tmpjs.put("attendance",js);
			}
			list.add(tmpjs);
		}
		/*for (Map.Entry<String, Object> en : tmpData.entrySet()){
			JSONObject js = new JSONObject();
			js.put("date", en.getKey());
			js.put("attendance", en.getValue());
			list.add(js);
		}*/
		data.put("code", 0);
		data.put("data", list);
		return data;
	}

	@Override
	public Map<String, Object> searchAttendanceGroupByHotel(String startTime,
			String endTime, Integer areaCode, String price, Integer sortType) {
		Map<String, Object> result = new HashMap<String, Object>();
		float sumRoomCount = 0;//已提交的房量
		float authRoomCount = 0;//已确认的房量
		String avgDayRoomCount = "0" ;//日均房量
		String avgUserRoomCount =  "0";//人均房量
		String submitRatio =  "0";//提交率
		String authRatio =  "0";//确认率
		Integer sumUserCount = 0;//总人数
		float costMoney = 0;//酒店花费
		int noSubmitUserCount = 0;//未提交考勤的人数
		int days = 0;
		StringBuffer hotelIds = new StringBuffer("");
		if(StringUtils.isNotBlank(price)){
			List<Hotel> hli = hotelService.getHotelByConditione(price,null);
			for(int i=0; i<hli.size(); i++){
				if(i == 0){
					hotelIds.append(hli.get(i).getId().toString());
				}else{
					hotelIds.append(","+hli.get(i).getId().toString());
				}
			}
		}
		//全部
		StringBuffer sql1 = new StringBuffer("SELECT  h.id AS hotelid, h.hotelname AS hotelname, SUM(ct.roomcount) AS roomcount, SUM(ct.costmoney) AS costmoney, COUNT(ct.emid) as usercount FROM tb_task AS t, tb_child_task AS ct, tb_hotel AS h WHERE ct.taskid=t.taskid AND h.id=t.hotelid ");
		//已确认
		StringBuffer sql2 = new StringBuffer("SELECT  h.id AS hotelid, SUM(ct.roomcount) AS roomcount,SUM(ct.costmoney) AS costmoney FROM tb_task AS t, tb_child_task AS ct, tb_hotel AS h WHERE ct.taskid=t.taskid AND h.id=t.hotelid AND ct.result IN (3,4) ");
		//未提交
		StringBuffer sql3 = new StringBuffer("SELECT  h.id AS hotelid, COUNT(ct.emid) as usercount FROM tb_task AS t, tb_child_task AS ct, tb_hotel AS h WHERE ct.taskid=t.taskid AND h.id=t.hotelid ");
				
		if(StringUtils.isBlank(startTime)){//起始时间没输入
			result.put("code", -1);
			result.put("msg", "请选择起始日期");
			return result;
		}else{
			sql1.append(" AND ct.creattime >= '"+startTime+"' ");
			sql2.append(" AND ct.creattime >= '"+startTime+"' ");
			sql3.append(" AND ct.creattime >= '"+startTime+"' ");
			if(StringUtils.isNotBlank(endTime)){
				sql1.append(" AND ct.creattime <= '"+endTime+" 23:59:59'");
				sql2.append(" AND ct.creattime <= '"+endTime+" 23:59:59'");
				sql3.append(" AND ct.creattime <= '"+endTime+" 23:59:59'");
			}
		}
		
		if(StringUtils.isBlank(endTime)){
			endTime = DateUtil.formatYMD(new Date());
		}
		
		days = DateUtil.daysDistance(DateUtil.parseYMD(startTime), DateUtil.parseYMD(endTime))+1;
		
		if(areaCode != null && areaCode.intValue()>100000){//区域码至少6位
			String w_areaCode = areaCode.toString();
			int x=0;
			String w_area="";
			if(w_areaCode.endsWith("0000")) {
				//精度 省份
				x=9999;
			}else if(w_areaCode.endsWith("00")) {
				//精度 市
				x=99;
			}else {
				//精度 区
				x=0;
			}
			//要的   - 条件  >=0
			//410610-410000 <= 9999
			//410610-410600 <= 99
			//410610-410610 == 0
			w_area=" and convert(h.area,signed) - "+areaCode +" >=0 ";
			w_area+=" and convert(h.area,signed) - "+areaCode +" <= "+x;
			sql1.append(w_area);
			sql2.append(w_area);
			sql3.append(w_area);
		}
		
		if(StringUtils.isNotBlank(hotelIds.toString())){
			sql1.append(" AND h.id IN ("+hotelIds.toString()+") ");
			sql2.append(" AND h.id IN ("+hotelIds.toString()+") ");
			sql3.append(" AND h.id IN ("+hotelIds.toString()+") ");
		}
		if(sortType.intValue() == 0){
			sql1.append(" AND ct.roomcount>=0 group by h.id ORDER BY h.hotelname DESC");
			sql2.append(" AND ct.roomcount>0 group by h.id ORDER BY h.hotelname DESC");
			sql3.append(" AND ct.roomcount=0 group by h.id ORDER BY h.hotelname DESC");
		}else if(sortType.intValue() == 1){
			sql1.append(" AND ct.roomcount>=0 group by h.id ORDER BY h.creattime DESC");
			sql2.append(" AND ct.roomcount>0 group by h.id ORDER BY h.creattime DESC");
			sql3.append(" AND ct.roomcount=0 group by h.id ORDER BY h.creattime DESC");
		}else{
			sql1.append(" AND ct.roomcount>=0 group by h.id ORDER BY h.hotelname DESC");
			sql2.append(" AND ct.roomcount>0 group by h.id ORDER BY h.hotelname DESC");
			sql3.append(" AND ct.roomcount=0 group by h.id ORDER BY h.hotelname DESC");
		}
		try {
			Map<String, NullableType> fieldMappingType = new HashMap<String, NullableType>();
			Map<Integer, AttendanceModel> tmp = new HashMap<Integer, AttendanceModel>();
			Map<Integer, AttendanceModel> tmp2 = new HashMap<Integer, AttendanceModel>();
			//已确认
			fieldMappingType.put("roomcount", Hibernate.FLOAT);
			fieldMappingType.put("hotelid", Hibernate.INTEGER);
			fieldMappingType.put("costmoney", Hibernate.FLOAT);
			List<AttendanceModel>  data2 = entityManage.findObjectsByFullSql(sql2.toString(), AttendanceModel.class, fieldMappingType);
			for(AttendanceModel at: data2){
				tmp.put(at.getHotelid(), at);
				authRoomCount = authRoomCount + at.getRoomcount();//已确认的总房量
				costMoney = costMoney + at.getCostmoney();//酒店花费
			}
			
			//未提交
			fieldMappingType.clear();
			fieldMappingType.put("usercount", Hibernate.INTEGER);
			fieldMappingType.put("hotelid", Hibernate.INTEGER);
			List<AttendanceModel>  data3 = entityManage.findObjectsByFullSql(sql3.toString(), AttendanceModel.class, fieldMappingType);
			for(AttendanceModel at: data3){
				tmp2.put(at.getHotelid(), at);
			}
			
			//全部
			fieldMappingType.clear();
			fieldMappingType.put("hotelid", Hibernate.INTEGER);
			fieldMappingType.put("hotelname", Hibernate.STRING);
			fieldMappingType.put("roomcount", Hibernate.FLOAT);
			fieldMappingType.put("costmoney", Hibernate.FLOAT);
			fieldMappingType.put("usercount", Hibernate.INTEGER);
			List<AttendanceModel> returnData = new ArrayList<AttendanceModel>();
			List<AttendanceModel>  data = entityManage.findObjectsByFullSql(sql1.toString(), AttendanceModel.class, fieldMappingType);
			for(AttendanceModel at: data){
				sumUserCount = sumUserCount + at.getUsercount();//总人数
				AttendanceModel authAt2 = tmp2.get(at.getHotelid());
				if(authAt2 == null){//都提交了
					at.setNoSubmitUserCount(0);
				}else{
					at.setNoSubmitUserCount(authAt2.getUsercount());
				}
				noSubmitUserCount = noSubmitUserCount + at.getNoSubmitUserCount();//未提交人数
				/*if(at.getRoomcount() == 0){
					continue;
				}*/
				AttendanceModel authAt = tmp.get(at.getHotelid());
				if(authAt == null){//全部都未确认
					at.setAuthRoomcount(0f);
					at.setCostmoney(0f);//酒店支出\
				}else{
					at.setAuthRoomcount(authAt.getRoomcount());
					at.setCostmoney(authAt.getCostmoney()==null?0:authAt.getCostmoney());//酒店支出
				}
				at.setSubmitUserCount(at.getUsercount()-at.getNoSubmitUserCount());//已提交的人数
				at.setSubmitRatio(fnum.format(((at.getSubmitUserCount().floatValue()/at.getUsercount().floatValue())) * 100));//提交率
				if(at.getRoomcount() == 0){
					at.setAuthRatio(fnum.format(0));//没有提交的房量
				}else{
					at.setAuthRatio(fnum.format((at.getAuthRoomcount()/at.getRoomcount()) * 100));//确认率
				}
				if(at.getSubmitUserCount() == 0){
					at.setAvgUserRoomCount("0");//人均
				}else{
					at.setAvgUserRoomCount(fnum.format(at.getRoomcount()/at.getSubmitUserCount()));//人均
				}
				
				at.setAvgDayRoomCount(fnum.format(at.getRoomcount()/days));//日均
				sumRoomCount = sumRoomCount + at.getRoomcount();//已提交的总房量
				returnData.add(at);
			}
			avgDayRoomCount = fnum.format(sumRoomCount/days);
			//提交人数（房量>0的帮工）
			Float submitUserCount = (float) (sumUserCount-noSubmitUserCount);
			//人均日房量只算已经提交的人
			avgUserRoomCount = fnum.format(sumRoomCount/(submitUserCount.floatValue()== 0 ? 1 : submitUserCount.floatValue()));
			
			submitRatio = fnum.format((submitUserCount / (sumUserCount == 0 ? 1 : sumUserCount))*100);
			authRatio = fnum.format(authRoomCount/(sumRoomCount == 0 ? 1 : sumRoomCount) * 100);
			
			result.put("data", returnData);
			result.put("avgDayRoomCount", avgDayRoomCount);
			result.put("avgUserRoomCount", avgUserRoomCount);
			result.put("submitRatio", submitRatio);
			result.put("authRatio", authRatio);
			result.put("sumRoomCount", fnum.format(sumRoomCount));
			result.put("authRoomCount", fnum.format(authRoomCount));
			result.put("sumUserCount", sumUserCount);
			result.put("submitUserCount", fnum.format(submitUserCount));
			result.put("costMoney", moneyfnum.format(costMoney));
		} catch (Exception e) {
			e.printStackTrace();
		}
		result.put("code", 0);
		return result;
	}
	
	
	@Override
	public Map<String, Object> searchAttendanceGroupByDate(String startTime,
			String endTime, Integer areaCode, String price, Integer sortType) {
		Map<String, Object> result = new HashMap<String, Object>();
		float sumRoomCount = 0;//已提交的房量
		float authRoomCount = 0;//已确认的房量
		String avgDayRoomCount = "0" ;//日均房量
		String avgUserRoomCount =  "0";//人均房量
		String submitRatio =  "0";//提交率
		String authRatio =  "0";//确认率
		Integer sumUserCount = 0;//总人数
		float costMoney = 0;//酒店花费
		int noSubmitUserCount = 0;//未提交考勤的人数
		int days = 0;
		StringBuffer hotelIds = new StringBuffer("");
		if(StringUtils.isNotBlank(price)){
			List<Hotel> hli = hotelService.getHotelByConditione(price, null);
			for(int i=0; i<hli.size(); i++){
				if(i == 0){
					hotelIds.append(hli.get(i).getId().toString());
				}else{
					hotelIds.append(","+hli.get(i).getId().toString());
				}
			}
		}
		//全部
		StringBuffer sql1 = new StringBuffer("SELECT  ct.creattime AS creattime, SUM(ct.roomcount) AS roomcount, SUM(ct.costmoney) AS costmoney, COUNT(ct.emid) as usercount FROM tb_task AS t, tb_child_task AS ct, tb_hotel AS h  WHERE ct.taskid=t.taskid AND h.id=t.hotelid  ");
		//已确认
		StringBuffer sql2 = new StringBuffer("SELECT  ct.creattime AS creattime, SUM(ct.roomcount) AS roomcount,SUM(ct.costmoney) AS costmoney FROM tb_task AS t, tb_child_task AS ct, tb_hotel AS h  WHERE ct.taskid=t.taskid AND ct.result IN (3,4) AND h.id=t.hotelid  ");
		//未提交
		StringBuffer sql3 = new StringBuffer("SELECT  ct.creattime AS creattime, COUNT(ct.emid) as usercount FROM tb_task AS t, tb_child_task AS ct, tb_hotel AS h WHERE ct.taskid=t.taskid AND h.id=t.hotelid  ");
				
		if(StringUtils.isBlank(startTime)){//起始时间没输入
			result.put("code", -1);
			result.put("msg", "请选择起始日期");
			return result;
		}else{
			sql1.append(" AND ct.creattime >= '"+startTime+"' ");
			sql2.append(" AND ct.creattime >= '"+startTime+"' ");
			sql3.append(" AND ct.creattime >= '"+startTime+"' ");
			if(StringUtils.isNotBlank(endTime)){
				sql1.append(" AND ct.creattime <= '"+endTime+" 23:59:59'");
				sql2.append(" AND ct.creattime <= '"+endTime+" 23:59:59'");
				sql3.append(" AND ct.creattime <= '"+endTime+" 23:59:59'");
			}
		}
		
		if(StringUtils.isBlank(endTime)){
			endTime = DateUtil.formatYMD(new Date());
		}
		
		days = DateUtil.daysDistance(DateUtil.parseYMD(startTime), DateUtil.parseYMD(endTime))+1;
		
		if(areaCode != null && areaCode.intValue()>100000){//区域码至少6位
			String w_areaCode = areaCode.toString();
			int x=0;
			String w_area="";
			if(w_areaCode.endsWith("0000")) {
				//精度 省份
				x=9999;
			}else if(w_areaCode.endsWith("00")) {
				//精度 市
				x=99;
			}else {
				//精度 区
				x=0;
			}
			//要的   - 条件  >=0
			//410610-410000 <= 9999
			//410610-410600 <= 99
			//410610-410610 == 0
			w_area=" and convert(h.area,signed) - "+areaCode +" >=0 ";
			w_area+=" and convert(h.area,signed) - "+areaCode +" <= "+x;
			sql1.append(w_area);
			sql2.append(w_area);
			sql3.append(w_area);
		}
		
		if(StringUtils.isNotBlank(hotelIds.toString())){
			sql1.append(" AND h.id IN ("+hotelIds.toString()+") ");
			sql2.append(" AND h.id IN ("+hotelIds.toString()+") ");
			sql3.append(" AND h.id IN ("+hotelIds.toString()+") ");
		}
		
		sql1.append(" AND ct.roomcount>=0 GROUP BY ct.creattime ORDER BY ct.creattime DESC");
		sql2.append(" AND ct.roomcount>0 GROUP BY ct.creattime ORDER BY ct.creattime DESC");
		sql3.append(" AND ct.roomcount=0 GROUP BY ct.creattime ORDER BY ct.creattime DESC");
		try {
			Map<String, NullableType> fieldMappingType = new HashMap<String, NullableType>();
			Map<String, AttendanceModel> tmp = new HashMap<String, AttendanceModel>();
			Map<String, AttendanceModel> tmp2 = new HashMap<String, AttendanceModel>();
			//已确认
			fieldMappingType.put("roomcount", Hibernate.FLOAT);
			fieldMappingType.put("creatTime", Hibernate.DATE);
			fieldMappingType.put("costmoney", Hibernate.FLOAT);
			List<AttendanceModel>  data2 = entityManage.findObjectsByFullSql(sql2.toString(), AttendanceModel.class, fieldMappingType);
			for(AttendanceModel at: data2){
				tmp.put(DateUtil.formatYMD(at.getCreatTime()), at);
				authRoomCount = authRoomCount + at.getRoomcount();//已确认的总房量
				costMoney = costMoney + at.getCostmoney();//酒店花费
			}
			
			//未提交
			fieldMappingType.clear();
			fieldMappingType.put("usercount", Hibernate.INTEGER);
			fieldMappingType.put("creatTime", Hibernate.DATE);
			List<AttendanceModel>  data3 = entityManage.findObjectsByFullSql(sql3.toString(), AttendanceModel.class, fieldMappingType);
			for(AttendanceModel at: data3){
				tmp2.put(DateUtil.formatYMD(at.getCreatTime()), at);
			}
			
			//全部
			fieldMappingType.clear();
			fieldMappingType.put("creatTime", Hibernate.DATE);
			fieldMappingType.put("roomcount", Hibernate.FLOAT);
			fieldMappingType.put("costmoney", Hibernate.FLOAT);
			fieldMappingType.put("usercount", Hibernate.INTEGER);
			List<AttendanceModel> returnData = new ArrayList<AttendanceModel>();
			List<AttendanceModel>  data = entityManage.findObjectsByFullSql(sql1.toString(), AttendanceModel.class, fieldMappingType);
			for(AttendanceModel at: data){
				sumUserCount = sumUserCount + at.getUsercount();//总人数
				AttendanceModel authAt2 = tmp2.get(DateUtil.formatYMD(at.getCreatTime()));
				if(authAt2 == null){//都提交了
					at.setNoSubmitUserCount(0);
				}else{
					at.setNoSubmitUserCount(authAt2.getUsercount());
				}
				noSubmitUserCount = noSubmitUserCount + at.getNoSubmitUserCount();//未提交人数
				
				/*if(at.getRoomcount() == 0){
					continue;
				}*/
				AttendanceModel authAt = tmp.get(DateUtil.formatYMD(at.getCreatTime()));
				if(authAt == null){//全部都未确认
					at.setAuthRoomcount(0f);
					at.setCostmoney(0f);//酒店支出
				}else{
					at.setAuthRoomcount(authAt.getRoomcount());
					at.setCostmoney(authAt.getCostmoney()==null?0:authAt.getCostmoney());//酒店支出
				}
				
				at.setSubmitUserCount(at.getUsercount()-at.getNoSubmitUserCount());//已提交的人数
				at.setSubmitRatio(fnum.format(((at.getSubmitUserCount().floatValue()/at.getUsercount().floatValue())) * 100));//提交率
				if(at.getAuthRoomcount() == 0){
					at.setAuthRatio(fnum.format(0));//一个都没确认
				}if(at.getRoomcount() == 0){
					at.setAuthRatio(fnum.format(100));//没有提交的房量
				}else{
					at.setAuthRatio(fnum.format((at.getAuthRoomcount()/at.getRoomcount()) * 100));//确认率
				}
				
				if(at.getUsercount() == 0){
					at.setAvgUserRoomCount("0");//人均
				}else{
					at.setAvgUserRoomCount(fnum.format(at.getRoomcount()/at.getUsercount()));//人均
				}
				
				at.setAvgDayRoomCount(fnum.format(at.getRoomcount()/days));//日均
				sumRoomCount = sumRoomCount + at.getRoomcount();//已提交的总房量
				returnData.add(at);
			}
			avgDayRoomCount = fnum.format(sumRoomCount/days);
			//提交人数（房量>0的帮工）
			Float submitUserCount = (float) (sumUserCount-noSubmitUserCount);
			//人均日房量只算已经提交的人
			avgUserRoomCount = fnum.format(sumRoomCount/(submitUserCount.floatValue()== 0 ? 1 : submitUserCount.floatValue()));
			
			submitRatio = fnum.format((submitUserCount / (sumUserCount == 0 ? 1 : sumUserCount))*100);
			authRatio = fnum.format(authRoomCount/(sumRoomCount == 0 ? 1 : sumRoomCount) * 100);
			
			result.put("data", returnData);
			result.put("avgDayRoomCount", avgDayRoomCount);
			result.put("avgUserRoomCount", avgUserRoomCount);
			result.put("submitRatio", submitRatio);
			result.put("authRatio", authRatio);
			result.put("sumRoomCount", sumRoomCount);
			result.put("authRoomCount", authRoomCount);
			result.put("sumUserCount", sumUserCount);
			result.put("submitUserCount", submitUserCount);
			result.put("costMoney", costMoney);
		} catch (Exception e) {
			e.printStackTrace();
		}
		result.put("code", 0);
		return result;
	}
}
